<?php
require_once($_SERVER['DOCUMENT_ROOT'].'/ukkepuk/source/php/classes/model/Kid.php'); 
require_once($_SERVER['DOCUMENT_ROOT'].'/ukkepuk/source/php/classes/utils/CalendarUtils.php'); 
require_once($_SERVER['DOCUMENT_ROOT'].'/ukkepuk/source/php/classes/dao/AbstractDao.php'); 
require_once($_SERVER['DOCUMENT_ROOT'].'/ukkepuk/source/php/classes/validation/ActionResult.php'); 
require_once($_SERVER['DOCUMENT_ROOT'].'/ukkepuk/source/php/classes/model/KidReport.php'); 

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of KidDao
 *
 * @author virtual
 */
class KidDao extends AbstractDao{
    
    public function saveInOut($kidInOut) {
        $actionResult = new ActionResult();
        $actionResult->savetype = "saveInOut";
        
        if($this->inOutExists($kidInOut)){
            if($this->inOutUpdate($kidInOut)){
                $actionResult->success = true;
                $actionResult->addActionParameter($kidInOut);
            }
        }else {
            if($this->inOutInsert($kidInOut)){
                $actionResult->success = true;
                $actionResult->addActionParameter($kidInOut);
            }
        }
        
        return $actionResult;
        

    }
    
    private function inOutUpdate($kidInOut) {
        $kidId = $kidInOut->getKidsId();
        $date = $kidInOut->getDate();
        $inTime  = $kidInOut->getInTime();
        $outTime = $kidInOut->getOutTime();

        $setStmt = "";
        if($inTime != null)
            $setStmt = "`IN` = ?";
        if($outTime != null){
            if($setStmt != "")
                $setStmt .= ", ";
            
            $setStmt .= "`OUT` = ?";
        }
        
                
        $queryKid = $this->mysqli->prepare("UPDATE kids_in_out SET $setStmt WHERE KIDS_ID = ? and DATE = ?");

        if($inTime != null && $outTime != null)
            $queryKid->bind_param("ssis", $inTime, $outTime, $kidId, $date);
        else if($inTime != null && $outTime == null)
            $queryKid->bind_param("sis", $inTime, $kidId, $date);
        else if($inTime == null && $outTime != null)
            $queryKid->bind_param("sis", $outTime, $kidId, $date);
        
        if($queryKid->execute()){
            $queryKid->close();
            return true;
        }else {
            $queryKid->close();
            return false;
        }
       
        
    }
    
    private function inOutInsert($kidInOut){
        $kidId = $kidInOut->getKidsId();
        $date = $kidInOut->getDate();
        $inTime  = $kidInOut->getInTime();
        $outTime = $kidInOut->getOutTime();
        
        $queryKid = $this->mysqli->prepare("INSERT INTO kids_in_out(KIDS_ID, DATE, `IN`, `OUT`) VALUES(?,?,?,?)");
        $queryKid->bind_param("isss", $kidId, $date, $inTime, $outTime);
        
        if($queryKid->execute()){
            $queryKid->close();
            return true;
        }else {
            $queryKid->close();
            return false;
        }
        
    }
    
    private function inOutExists($kidInOut) {
        $kidId = $kidInOut->getKidsId();
        $date = $kidInOut->getDate();
        $queryKid = $this->mysqli->prepare("SELECT count(*) FROM kids_in_out k WHERE k.KIDS_ID = ? and DATE = ?");
        $queryKid->bind_param("is", $kidId, $date);
   
        $queryKid->execute();
        $queryKid->bind_result($count);
        $queryKid->fetch();
        
        $queryKid->close();
        
        return ($count > 0);
    }
    
    public function readKidFamilyAndAdmin($kid){
        $kidId = $kid->getId();

        $queryKid = $this->mysqli->prepare("SELECT k.NAME, k.FIRST_NAME, k.PROFILE_IMAGE FROM kids k WHERE k.ID = ?");
        $queryKid->bind_param("i", $kidId);
        $queryKid->execute();
        $queryKid->bind_result($name, $firstname, $profileImage);
        $queryKid->fetch();
        $result = "<kid id=\"$kidId\" name=\"$name\" firstname=\"$firstname\" profileImage=\"$profileImage\" date=\"$today\">";
        $queryKid->close();

        $queryRole = $this->mysqli->prepare("SELECT u.ID, u.USERNAME FROM user_kids uk, users u WHERE uk.USER_ID = u.ID and uk.KIDS_ID = ?");
        $queryRole->bind_param("i", $kidId);
        $queryRole->execute();
        $queryRole->bind_result($id, $username);
        $queryRole->fetch();
        $result .= "<user id=\"".$id."\" username=\"".$username."\" />";
        $queryRole->close();
        
        
        
        $result .= "</kid>";
        
    }
    //return list of kid
    public function readKidInfoXml($kid, $today) {
        
        
        $kidId = $kid->getId();

        //READ KID INFO
        $queryKid = $this->mysqli->prepare("SELECT k.NAME, k.FIRST_NAME, k.PROFILE_IMAGE FROM kids k WHERE k.ID = ?");
        $queryKid->bind_param("i", $kidId);
        $queryKid->execute();
        $queryKid->bind_result($name, $firstname, $profileImage);
        $queryKid->fetch();
        $result = "<kid id=\"$kidId\" name=\"$name\" firstname=\"$firstname\" profileImage=\"$profileImage\" date=\"$today\"";
        $queryKid->close();

        //READ KID IN OUT
        $queryInOut = $this->mysqli->prepare("SELECT `IN`, `OUT` FROM kids_in_out WHERE KIDS_ID = ? and date_format(DATE, '%Y%m%d') = ?");
        $queryInOut->bind_param("is", $kidId, $today);
        $queryInOut->execute();
        $queryInOut->bind_result($in, $out);
        $queryInOut->fetch();
        $result .= " in=\"$in\" out=\"$out\">";
        $queryInOut->close();


        //READ DIAPERS FOR TODAY
        $queryKidDiapers =  $this->mysqli->prepare("SELECT date_format(d.TIMESTAMP, '%H:%i'), d.TYPE FROM diapers d WHERE date_format(d.TIMESTAMP, '%Y%m%d') = ? AND d.KIDS_ID = ? ORDER BY d.TIMESTAMP");
        $queryKidDiapers->bind_param("si", $today, $kidId);
        $queryKidDiapers->execute();
        $queryKidDiapers->bind_result($time, $type);
        $result .= "<diapers>";
        while($queryKidDiapers->fetch()){
            $result .= "<diaper time=\"".$time."\" type=\"".$type."\"/>";
        }
        $result .= "</diapers>";

        //READ SLEEP FOR TODAY
        $queryKidSleep = $this->mysqli->prepare("SELECT date_format(s.SLEEP_FROM, '%H:%i'), date_format(s.SLEEP_TO, '%H:%i') FROM sleep s WHERE date_format(s.SLEEP_FROM, '%Y%m%d') = ? AND s.KIDS_ID = ? ORDER BY s.SLEEP_FROM");
        $queryKidSleep->bind_param("si", $today, $kidId);
        $queryKidSleep->execute();
        $queryKidSleep->bind_result($sleepFrom, $sleepTo);
        $result .= "<sleeps>";
        while($queryKidSleep->fetch()){
            $result .= "<sleep sleepFrom=\"$sleepFrom\" sleepTo=\"$sleepTo\" />";
        }
        $result .= "</sleeps>";

        //READ DRINK FOR TODAY
        $queryKidDrink = $this->mysqli->prepare("SELECT dt.TYPE, AMOUNT, date_format(d.DRINK_FROM, '%H:%i'), date_format(d.DRINK_TO, '%H:%i') FROM drinks d, drink_types dt WHERE d.DRINK_TYPES_ID = dt.ID AND date_format(d.DRINK_FROM, '%Y%m%d') = ? AND d.KIDS_ID = ? ORDER BY d.DRINK_FROM");
        $queryKidDrink->bind_param("si", $today, $kidId);
        $queryKidDrink->execute();
        $queryKidDrink->bind_result($drinkType, $amount, $timefrom, $timeto);
        $result .= "<drinks>";
        while($queryKidDrink->fetch()){
            $result .= "<drink timeFrom=\"".$timefrom."\" timeTo=\"" . $timeto . "\" amount=\"".$amount."\" type=\"".$drinkType."\"/>";
        }
        $result .= "</drinks>";


        //READ FOOD FOR TODAY
        $queryKidFood = $this->mysqli->prepare("SELECT r.TITLE, date_format(f.EAT_FROM, '%H:%i'),date_format(f.EAT_TO, '%H:%i'), f.score FROM food f, recipe r WHERE f.RECIPE_ID = r.ID AND date_format(f.EAT_FROM, '%Y%m%d') = ? AND f.KIDS_ID = ? ORDER BY f.EAT_FROM");
        $queryKidFood->bind_param("si", $today, $kidId);
        $queryKidFood->execute();
        $queryKidFood->bind_result($title, $timefrom, $timeto, $score);
        $result .= "<foods>";
        while($queryKidFood->fetch()){
            $result .= "<food timeFrom=\"".$timefrom."\" timeTo=\"" .$timeto. "\" title=\"".$title."\" score=\"".$score."\"/>";
        }
        $result .= "</foods>";


        //READ TEMPERATURE FOR KID
        $queryKidTemperature = $this->mysqli->prepare("SELECT date_format(TIMESTAMP, '%H:%i'), DEGREE FROM temperature  WHERE date_format(TIMESTAMP, '%Y%m%d') = ? AND KIDS_ID = ? ORDER BY TIMESTAMP");
        $queryKidTemperature->bind_param("si", $today, $kidId);
        $queryKidTemperature->execute();
        $queryKidTemperature->bind_result($time, $degree);
        $result .= "<temperatures>";
        while($queryKidTemperature->fetch()){
            $result .= "<temperature time=\"".$time."\" degree=\"" .$degree. "\"/>";
        }
        $result .= "</temperatures>";


        $result .= "</kid>";
        return $result;

    }
    
    public function saveTemperature($temperature){
        
        $kidId = $temperature->getKidsId();
        $degree = $temperature->getDegree();
        
        $actionResult = new ActionResult();
        $actionResult->savetype = "saveTemperature";
        
        $saveTemperature = $this->mysqli->prepare("INSERT INTO temperature(KIDS_ID, TIMESTAMP, DEGREE) VALUES (?,?,?)");
        $dateTime = $temperature->getTimestampString();
        $saveTemperature->bind_param("iss", $kidId, $dateTime, $degree);

        if($saveTemperature->execute()){
            //if execute success, set succes to true
            $actionResult->success = true;
            //and add diaper in the result
            $actionResult->addActionParameter($temperature);
        }else {
            //if error during execution set reason of error in the result object
            $actionResult->error = $this->mysqli->error;
        }
        //close the statement
        $saveTemperature->close();

        //return the actionresult
        return $actionResult;
    }
    
    public function saveDiaperForKid($diaper) {
        $kidId = $diaper->getKidsId();
        $diaperType = $diaper->getType();
       
        //create a result action object. this contains info about the result of the execution of this method
        $actionResult = new ActionResult();
        //set the executed action in the result
        $actionResult->savetype = "saveDiperForKid";
        //save diaper info for kid
        $saveDiaper = $this->mysqli->prepare("INSERT INTO diapers(KIDS_ID, TIMESTAMP, TYPE) VALUES (?,?,?)");
        $dateTime = $diaper->getTimestampString();
        
        
        $saveDiaper->bind_param("isi", $kidId, $dateTime, $diaperType);
        if($saveDiaper->execute()){
            //if execute success, set succes to true
            $actionResult->success = true;
            //and add diaper in the result
            $actionResult->addActionParameter($diaper);
        }else {
            //if error during execution set reason of error in the result object
            $actionResult->error = $this->mysqli->error;
        }
        //close the statement
        $saveDiaper->close();

        //return the actionresult
        return $actionResult;
    }
    
    public function saveSleepForKid ($sleep) {
        $actionResult = new ActionResult();
        $actionResult->savetype = "saveSleepForKid";
        $kidsId = $sleep->getKidsId();
        if($sleep->getSleepFrom() != null) {
            $dateTime = $sleep->getSleepFromTimestampString();
            $querySleepInsertFrom = $this->mysqli->prepare("INSERT INTO sleep (KIDS_ID, SLEEP_FROM) VALUES (?, ?)");	
            $querySleepInsertFrom->bind_param("is", $kidsId, $dateTime);
            if($querySleepInsertFrom->execute()){
                $actionResult->success = true;
                $actionResult->addActionParameter($sleep);
            }else {
                $actionResult->error = $this->mysqli->error;
            }
            $querySleepInsertFrom->close();
        }
        if($sleep->getSleepTo() != null) {
            $querySleepUpdateTo = $this->mysqli->prepare("UPDATE sleep SET SLEEP_TO = ? WHERE KIDS_ID = ? AND SLEEP_TO IS NULL");	
            $dateTime = $sleep->getSleepToTimestampString();
            
            $querySleepUpdateTo->bind_param("si", $dateTime, $kidsId);
            if($querySleepUpdateTo->execute()){
                $actionResult->success = true;
                $sleep->begin = false;
                $actionResult->addActionParameter($sleep);
            }else {
                $actionResult->error = $this->mysqli->error;
            }
            $querySleepUpdateTo->close();
            
        }
			
        return $actionResult;
    }
    
    public function saveDrinkForKid($drink) {
        $actionResult = new ActionResult();
		$actionResult->savetype = "saveLiquidForKid";

        $kidId = $drink->getKidsId();
        
        if($drink->getDrinkFrom() != null) {
            $type = $drink->getDrinkTypesId();
       
            $queryDrink = $this->mysqli->prepare("INSERT INTO drinks(KIDS_ID, DRINK_TYPES_ID, DRINK_FROM) VALUES (?,?,?)");
            $dateTime = $drink->getDrinkFromTimestampString();
            $queryDrink->bind_param("iis", $kidId, $type, $dateTime);
            if($queryDrink->execute()){
                //double check food title saved
                $queryDrinkType = $this->mysqli->prepare("SELECT TYPE FROM drink_types WHERE ID = ?");
                $drinkTypeId = $drink->getDrinkTypesId();
                $queryDrinkType->bind_param("i", $drinkTypeId);

                if($queryDrinkType->execute()){
                    $actionResult->success = true;
                    $queryDrinkType->bind_result($type);
                    $queryDrinkType->fetch();
                    $drink->setDrinkTypeString($type);

                   
                }else { 
                    $actionResult->error = $this->mysqli->error;
                }

                $queryDrinkType->close();
                $queryDrink->close();
                $actionResult->addActionParameter($drink);                
            } else {
                $actionResult->error = $this->mysqli->error;
            }
        }else if($drink->getDrinkTo() != null) {
            //echo "ELSE: " . var_dump($food->getEatTo());
            $amount = $drink->getAmount();
            $queryFood = $this->mysqli->prepare("UPDATE drinks SET DRINK_TO = ?, AMOUNT = ? WHERE KIDS_ID = ? AND DRINK_TO IS NULL");
            $dateTime = $drink->getDrinkToTimestampString();
            $queryFood->bind_param("sii", $dateTime, $amount, $kidId);
            if($queryFood->execute()){
                    $actionResult->success = true;
                    $actionResult->addActionParameter($drink);
            }else {
                $actionResult->error = $this->mysqli->error;
            }
            $queryFood->close();

        }
       
        
        return $actionResult;
    }
    
    public function saveFoodForKid($food) {
        $actionResult = new ActionResult();
        $actionResult->savetype = "saveFoodForKid";

        $kidId = $food->getKidsId();
        
        if($food->getEatFrom() != null) {
            //echo "IF: " . var_dump($food->getEatFrom());
            $menuId = $food->getRecipeId();
            $queryFood = $this->mysqli->prepare("INSERT INTO food(KIDS_ID, RECIPE_ID, EAT_FROM) VALUES (?,?,?)");
            $dateTime = $food->getEatFromTimestampString();
            $queryFood->bind_param("iis", $kidId, $menuId, $dateTime);
            if($queryFood->execute()){
                //double check food title saved
                $queryFoodTitle = $this->mysqli->prepare("SELECT TITLE FROM recipe WHERE ID = ?");
                $queryFoodTitle->bind_param("i", $menuId);
                if($queryFoodTitle->execute()){
                    $actionResult->success = true;
                    $queryFoodTitle->bind_result($title);
                    $queryFoodTitle->fetch();
                    $food->setRecipeString($title);

                }else { 
                    $actionResult->error = $this->mysqli->error;
                }

                $queryFoodTitle->close();
                $queryFood->close();
                $actionResult->addActionParameter($food);                
            } else {
                    $actionResult->error = $this->mysqli->error;
                
            }
    
        }else if($food->getEatTo() != null) {
            //echo "ELSE: " . var_dump($food->getEatTo());
            $score = $food->getScore();
            $queryFood = $this->mysqli->prepare("UPDATE food SET EAT_TO = ?, SCORE = ? WHERE KIDS_ID = ? AND EAT_TO IS NULL");
            $dateTime = $food->getEatToTimestampString();
            $queryFood->bind_param("sii", $dateTime, $score, $kidId);
            if($queryFood->execute()){
                    $actionResult->success = true;
                    $actionResult->addActionParameter($food);
            }else {
                $actionResult->error = $this->mysqli->error;
            }
            $queryFood->close();

        }
       

        return $actionResult;
    }
    
    public function readKidsCommingOnDay($key){
        $today = CalendarUtils::getTodayDateKey();	
        
        $queryCalendar = $this->mysqli->prepare("select k.ID, date_format(c.DATE, '%Y%m%d'), k.NAME, k.FIRST_NAME, c.START_TIME, c.END_TIME from kids k, calendar c where k.ID = c.KIDS_ID and date_format(c.DATE, '%Y%m%d') = ?");
        $queryCalendar->bind_param("s", $today);
        $queryCalendar->execute();
		$queryCalendar->bind_result($kidId, $date, $kidName, $kidFirstName, $startTime, $endTime);
       
        $result = array();
        
        while($queryCalendar->fetch()){
            $tmpKid = new Kid();
            $tmpKid->setName($kidName);
            $tmpKid->setFirstName($kidFirstName);
            $tmpKid->setId($kidId);
            
            $tmpCalendar = new Calendar();
            $tmpCalendar->setStartTime($startTime);
            $tmpCalendar->setEndTime($endTime);
            $tmpKid->addCalendar($tmpCalendar);
            array_push($result, $tmpKid);
            
        }
       
        $queryCalendar->close();
         
        return $result;
    }
    
    public function readAllKidsSubscribed() {
        $result = Array();

        $queryKid = $this->mysqli->prepare("SELECT k.ID, k.NAME, k.FIRST_NAME, k.PROFILE_IMAGE FROM kids k");
        $queryKid->execute();
        $queryKid->bind_result($id, $name, $firstname, $profileImage);
        while($queryKid->fetch()){
            $kid = new Kid();
            $kid->setId($id);
            $kid->setName($name);
            $kid->setFirstName($firstname);
            $kid->setProfileImage($profileImage);
            array_push($result, $kid);
        }
        $queryKid->close();

        return $result;
		
    }
    
    public function saveNote($kidReport) {
        $actionResult = new ActionResult();
        $actionResult->savetype = "saveNote";
        $kidsId = $kidReport->getKidsId();
        $kidsNote = $kidReport->getReport();
        //first check if there is a record for kid and day
        $queryNoteCount = $this->mysqli->prepare("SELECT ID FROM kid_report WHERE kids_ID = ? and date_format(TIMESTAMP, '%Y%m%d') = ?");
        $dateKey = $kidReport->getDateKey();
        $queryNoteCount->bind_param('is', $kidsId, $dateKey);
        if($queryNoteCount->execute()){
            $queryNoteCount->store_result(); 
            $queryNoteCount->bind_result($id);
            $queryNoteCount->fetch();
            if($id){
                $queryNoteUpdate = $this->mysqli->prepare("UPDATE kid_report SET REPORT = ? WHERE ID = ?");

                $queryNoteUpdate->bind_param('si', $kidsNote, $id);
                if($queryNoteUpdate->execute()){
                    $actionResult->success = true;
                }else{
                    $actionResult->error = $this->mysqli->error;
                }
                $queryNoteUpdate->close();
            }else {
                $queryNote = $this->mysqli->prepare("INSERT INTO kid_report(kids_ID, REPORT, TIMESTAMP) VALUES (?,?,?)");
                $dateTime = $kidReport->getTimestampString();
                $queryNote->bind_param('iss', $kidsId, $kidsNote, $dateTime);

                if($queryNote->execute()){
                    $actionResult->success = true;
                }else{
                    $actionResult->error = $this->mysqli->error;
                }
                $queryNote->close();
            }


        }else{
            $actionResult->error = $this->mysqli->error;
        }
        $queryNoteCount->free_result();
        $queryNoteCount->close();

        //code here
        //if there is, update.
        //code here
        //if not, insert
        return $actionResult;
    }
        public function readNote($kidReport) {
        $actionResult = new ActionResult();
        $actionResult->savetype = "readNote";

        $queryNoteCount = $this->mysqli->prepare("SELECT REPORT FROM kid_report WHERE kids_ID = ? and date_format(TIMESTAMP, '%Y%m%d') = ?");
        $dateKey = $kidReport->getDateKey();
        $kidsId = $kidReport->getKidsId();
        $queryNoteCount->bind_param('is', $kidsId, $dateKey);

        if($queryNoteCount->execute()){
            $actionResult->success = true;
            $queryNoteCount->bind_result($report);
            $queryNoteCount->fetch();
            $kidReport->setReport($report);
            $actionResult->addActionParameter($kidReport);
        }else {
            $actionResult->error = $this->mysqli->error;

        }
        return $actionResult;
    }
}

?>
